# coding=utf-8
"""
根据数据表生成对应的实体
董新强
日期:2016-02-15
"""
import os
import platform

import pymysql

conn = pymysql.connect(host='dev.cedwy3bhqgon.rds.cn-north-1.amazonaws.com.cn',
                       port=19890,
                       user='dns-dev',
                       passwd='##dv_$$&123??',
                       db='information_schema', charset='utf8')
_tables = {}
_dir = ''


def get_tables():
    '''
    获取数据库中所有的表
    '''
    try:
        cur = conn.cursor()
        cur.execute("select TABLE_NAME, TABLE_COMMENT from TABLES WHERE TABLE_SCHEMA='db_cat' order by TABLE_NAME")
        print('表名:')
        for v in cur:
            _tables[v[0]] = v[1]
            print(v[0])
        cur.close()

        for tb in _tables:
            cur = conn.cursor()
            sql = "select COLUMN_NAME,ORDINAL_POSITION,IS_NULLABLE,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from columns  where table_schema='db_cat' and TABLE_NAME ='" + tb + "'"
            cur.execute(sql)

            write_to_file(cur, tb, _tables[tb])
            cur.close()
        print('完成表到实体的转换')

    except Exception as e:
        print('异常=>', e)
    finally:
        conn.close()


def write_to_file(row, tb, tbdesc):
    '''   -----------------------写入文件--------------------------------------   '''
    if (platform.system() == 'Linux'):
        path = _dir + r'/' + tb + '.py'
    else:
        path = _dir + '\\' + tb + ".py"
    f = open(path, 'w', encoding='utf-8')
    f.write('# coding=utf-8')
    f.write('\n')
    f.write('from datetime import datetime')
    f.write('\n')
    f.write('import time')
    f.write('\n')
    f.write('from sqlalchemy import Column, Integer, String, DateTime, Numeric, Boolean, Binary, BigInteger,Float')
    f.write('\n')
    f.write('from models.orm_base import BaseOrmClass')
    f.write('\n')
    f.write('class ')
    f.write(tb.title().replace('_', ""))
    f.write('(BaseOrmClass):')
    f.write('\n')
    f.write("    '''\n")
    f.write('    模型(表)备注:\n')
    f.write(tbdesc)
    f.write('\n')
    f.write("    '''")
    f.write('\n')
    f.write("    __tablename__ = '")
    f.write(tb + "'")
    f.write('\n\n')

    reprs = []
    dicstr = []
    setstr = []
    for v in row:
        item = '    %s=Column(%s' % (v[0], getType(v[4]),)
        setstr.append('self.%s = obj.get(\'%s\')' % (v[0], v[0]))
        if v[5] == 'PRI':
            item += ', primary_key=True'
            comment = "    '''   " + v[6] + "   '''"
            dicstr.append("\n                '%s':self.%s" % (v[0], v[0]))
        elif v[2] == 'NO':
            item += ', nullable=False'
            comment = "    '''   " + v[6] + "   '''"
            reprs.append("%s='{self.%s}'" % (v[0], v[0]))

            dicstr.append("\n                '%s':self.%s" % (v[0], v[0]))
        else:
            comment = "    '''   " + v[6] + "   '''"
            dicstr.append("\n                '%s':self.%s" % (v[0], v[0]))
        item += ')'
        f.write(comment)
        f.write('\n')
        f.write(item)
        f.write('\n\n')

    '''   构造__repr__函数   '''
    if (len(reprs) > 0):
        f.write('\n')
        f.write('    def __repr__(self):')
        f.write('\n')
        f.write("        return \"")
        f.write(tb)
        f.write("(")
        f.write(','.join(reprs))
        f.write(")\"")
        f.write('.format(self=self)')

    '''   构造字典函数   '''
    f.write('\n\n')

    f.write('    def exclude(self): return ()\n')

    '''   -----------------------写入文件--------------------------------------   #endregion '''
    f.close()


def getType(tp):
    t = tp.split('(')
    if (len(t) > 1):
        t[1:2] = t[1].split(')')
    if t[0] == 'varchar':
        return 'String(%s)' % t[1]
    if t[0] == 'datetime':
        return 'DateTime(), default=datetime.now'
    if t[0] == 'int':
        return "Integer()"
    if t[0] == 'decimal':
        return 'Numeric(' + (t[1]) + ')'
    if t[0] == 'char':
        return 'String(%s)' % t[1]
    if t[0] == 'bit':
        return 'Boolean()'
    if t[0] == 'bigint':
        return 'BigInteger()'
    if t[0] == 'varbinary':
        return 'Binary(%s)' % t[1]
    if t[0] == 'text':
        return 'String()'
    if t[0] == 'double' or t[0] == 'float':
        return 'Float()'
    if t[0] == 'tinyint':
        return 'Boolean()'

    return tp


if __name__ == "__main__":
    system = platform.system()
    if system == 'Linux':
        _dir = r'/home/dns/models'
        if not os.path.exists(_dir):
            os.mkdir(_dir)
    else:
        _dir = r'd:\createdmodel'
        if not os.path.exists(_dir):
            os.mkdir(_dir)
    print('存储路径为=>', _dir)
    get_tables()
